﻿/*
{
"状态":1,
"名称":"生成-删除cmd脚本",
"作者":"马学兵",
"创建时间":"2018-07-21",
"功能":"生成-删除cmd脚本",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/


DECLARE @table_name VARCHAR(250)=''
DECLARE @Key VARCHAR(250)=''
DECLARE @Declare NVARCHAR(max)=''
DECLARE @Set NVARCHAR(max)=''
DECLARE @Rst NVARCHAR(MAX)=''

set @table_name='{table_name}'

if isnull(@table_name,'')=''
begin 
	RAISERROR ('SQL:表名不允许为空', 16 ,1 ) ; 
	return 
end 

if not exists(select 1 from y_table where table_name=@table_name)
begin 
	RAISERROR ('SQL:表名不存在', 16 ,1 ) ; 
	return 
end


SELECT @Key=@Key+' And '+colname +'=@'+colname
FROM dbo.y_columns INNER JOIN dbo.y_table ON y_table.table_key = y_columns.table_key
WHERE table_name=@table_name and iskey=1


SELECT @Declare=@Declare+'DECLARE @'+(case when datatype in ('real','float','smallmoney','varbinary','binary','char','nchar') then LOWER(colname)+' '+datatype+'('+convert(varchar(30),isnull(collength,''))+')'
              when datatype in ('nvarchar','varchar') and collength=-1 then  LOWER(colname)+' '+datatype+'(max)'
              when datatype in ('nvarchar','varchar') and collength<>-1 then LOWER(colname)+' '+datatype+'('+convert(varchar(30),isnull(collength,''))+')'
              when datatype in ('decimal','numeric') then LOWER(colname)+' '+datatype+'('+convert(varchar(30),isnull(collength,''))+','+convert(varchar(30),isnull(scale,''))+')'
              else LOWER(colname) +' '+datatype
         END) +CHAR(13)   FROM dbo.y_columns INNER JOIN dbo.y_table ON y_table.table_key = y_columns.table_key
WHERE table_name=@table_name and iskey=1
 
SELECT @Set=@Set+'Set @'+colname+'=''{'+LOWER(colname)+'}'''+CHAR(13)  
FROM dbo.y_columns INNER JOIN dbo.y_table ON y_table.table_key = y_columns.table_key
WHERE table_name=@table_name  and iskey=1


SET @Rst=
'
/*
{
"状态":0,
"名称":"删除-",
"作者":"",
"创建时间":"'+CONVERT(NVARCHAR(10),GETDATE(),121)+'",
"功能":"删除-",
"log": [
	{"date":"" , "dev" :"" , "content" : ""}
]
}
*/
'
 
SET @Rst=@Rst+@Declare+CHAR(13)+@Set+CHAR(13)+'DELETE FROM '+QUOTENAME(@table_name)+' WHERE '+STUFF(@Key,1,4,'') 
select @Rst as cmd 
